import cv2  # opencv库
import hyperlpr3 as lpr3  # 车牌识别模型
import psycopg2  # 连接数据库工具
import os  # 执行shell命令工具

# 连接数据库，参数根据需求更改
conn = psycopg2.connect(database="plate_number", user="omm", password="Bigdata@123", host="127.0.0.1", port="26000")
cur = conn.cursor()


print("Welcome to the license plate Identifying system.\n")
print("    exit                          --exit program")
print("    help                          --view instructions")
print("    identify                      --identify a image")
print("    shortcut                      --use quick functions")
print("    sql | SQL                     --enter SQL statement")
print("    upload                        --insert a image\n")


def sql_insert(ipath):  # 根据路径插入图片
    storepath = "/opt/gaussdb/image/"  # 图片存储目录
    global cur

    sql1 = "SELECT MAX(Id) FROM Plate;"  # 查找数据库中最大的Id
    cur.execute(sql1)
    res = cur.fetchall()
    newid = 1
    if res[0][0] is not None: newid = res[0][0] + 1  # 若数据库不为空，获取新Id

    newpath = storepath + f"{newid}" + ".png"  # 图片存储路径
    shell1 = f"rm -rf {newpath}"  # 删除被覆盖的图片
    shell2 = f"cp {ipath} {newpath}"  # 将图片复制到存储文件夹中
    os.system(shell1)
    os.system(shell2)
    sql2 = f"INSERT INTO Plate VALUES({newid},\'{newpath}\',CURRENT_TIMESTAMP,NULL,NULL);"  # 插入图片信息
    cur.execute(sql2)
    print(f"Successfully entered image with Id {newid}!")
    return newid


def hyperlpr_identify(id):  # 使用hyperlpr3模型识别车牌
    sql1 = f"SELECT Path FROM Plate WHERE Id = {id};"  # 获取图片信息
    cur.execute(sql1)
    res = cur.fetchall()
    if res[0][0] is None:  # 检验Id是否存在
        print("Error: There is no image with the Id in table Plate")
        return

    path = res[0][0]
    catcher = lpr3.LicensePlateCatcher()  # 实例化识别对象
    image = cv2.imread(path)  # 读取图片
    info = catcher(image)  # 识别结果
    number = f"\'{info[0][0]}\'"
    rel = info[0][1]
    sql2 = f"UPDATE Plate SET Number={number}, Reliability={rel} WHERE Id={id};"  # 将识别结果传回数据库
    cur.execute(sql2)
    print(f"Number:       {number}")
    print(f"Reliability:  {rel}")


def sql_dhow_res(res):  # 打印查询结果
    rows = len(res)
    lies = len(res[0])
    longest = [0,0,0,0,0]
    for i in range(0,lies):
        for j in range(0,rows):
            longest[i] = max(longest[i],len(str(res[j][i])))
    for row in res:
        for i in range(0,lies):
            lie = row[i]
            print(str(lie).ljust(longest[i]+5),end='')
        print("\n",end='')


while 1:
    print("# ", end='')
    inst = input()  # 指令
    inst_s = inst.split()  # 分割指令
    noinst = 1  # 无效指令标识符


    if inst == "help":  # 打印指令用法
        print("    exit                          --exit program")
        print("    help                          --view instructions")
        print("    identify <id>                 --identify a image with id")
        print("    shortcut <opt> [path]         --use quick functions")
        print("        opt: 1                    --insert a image with the path and identify it, path must be filled in")
        print("        opt: 2                    --query for the latest identification information")
        print("        opt: 3                    --query all valid identification information")
        print("        opt: 4                    --query all  information")
        print("    sql | SQL                     --enter and execute SQL statement")
        print("    upload <path>                 --insert a image with the path")
        continue


    if inst_s[0] == "shortcut":  # 快捷功能
        if len(inst_s) < 2:  # 检查参数
            print("Error: Parameters \"opt\" must be filled in!")
            continue

        opt = inst_s[1]

        if opt == "1":  # 插入图片并识别
            if len(inst_s) < 3:  # 检查是否输入路径
                print("Error: Parameters \"path\" must be filled in!")
                continue

            newid = sql_insert(inst_s[2])
            hyperlpr_identify(newid)
            continue

        if opt == "2":  # 查询最新的车牌识别信息
            sql1 = "SELECT MAX(Id) FROM Plate;"  # 查找数据库中最大的Id
            cur.execute(sql1)
            res = cur.fetchall()
            newid = 1
            if res[0][0] is not None: newid = int(res[0][0])   # 若数据库不为空，获取新Id
            else:
                print("Error: There is no data in table Plate!")
                continue
            sql2 = f"SELECT Id,Number,Reliability FROM Plate WHERE Id = {newid};"
            cur.execute(sql2)
            res = cur.fetchall()
            sql_dhow_res(res)
            continue

        if opt == "3":  # 查询所有有效的识别信息
            sql1 = f"SELECT Id,Number,Reliability FROM Plate WHERE Number IS NOT NULL;"
            cur.execute(sql1)
            res = cur.fetchall()
            sql_dhow_res(res)
            continue

        if opt == "4":  # 查询所有信息
            sql1 = f"SELECT * FROM Plate;"
            cur.execute(sql1)
            res = cur.fetchall()
            sql_dhow_res(res)
            continue


        print(f"Error: There is no function where opt equals {opt}!")
        continue


    if inst == "sql" or inst == "SQL":  # 输入SQL语句
        print("=> ",end='')
        sqlinst = ""
        while 1:
            sql = input()
            sqlinst = sqlinst + sql
            if sql[-1] == ';': break

        sqlinst_s = sqlinst.split()

        if sqlinst_s[0] == "select" or sqlinst_s[0] == "SELECT":  # 打印查询结果
            cur.execute(sqlinst)
            res = cur.fetchall()
            sql_dhow_res(res)
            continue

        continue


    if inst_s[0] == "upload":
        if len(inst_s) < 2:  # 检查是否输入path
            print("Error: Please enter the path!")
            continue

        path = inst_s[1]
        if os.path.exists(path) == 0:  # 检查文件是否存在
            print(f"Error: File with path \'{path}\' not found!")
            continue

        path = inst_s[1]
        newid = sql_insert(path)
        continue


    if inst_s[0] == "identify":
        if len(inst_s) < 2:  # 检查是否输入id
            print("Error: Please enter the id!")
            continue

        hyperlpr_identify(int(inst_s[1]))
        continue



    if inst == "exit":  # 退出程序
        break


    print("Error: Command not found!")  # 未知指令
    continue


conn.commit()
conn.close()



